using System.Web.Routing;
using AttributeRouting.Web.Mvc;
using System.Data.SqlClient;
using System.Configuration;
using $solutionNameChild$.Core.Manager;
using System;
using System.Data;
using System.Collections.Generic;
using System.Data.Common;
using System.Web;

[assembly: WebActivator.PostApplicationStartMethod(typeof($safeprojectname$.App_Start.DatabaseChecker), "Start")]
namespace $safeprojectname$.App_Start
{
    public static class DatabaseChecker
    {
        public static void Start()
        {
            string errorMessage = string.Empty;
            SqlConnection connect = null;
            try
            {
                string _connectionString = ConfigurationManager.ConnectionStrings[WebConfig.Configuration.ConnectionStrings.DefaultConnection.ToDescription()].ConnectionString;
                connect = new SqlConnection(_connectionString);
                try
                {
                    connect.Open();
                }
                catch (Exception)
                {
                    return;
                }


                string script = "SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME, * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME";
                DataSet ds = ExecureQueryToDataset(connect, script);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string storedProcedureName = "";
                    try
                    {
                        storedProcedureName = row.ItemArray[0].ToString();
                        //new SqlCommand("set showplan_text on; ", connect).ExecuteNonQuery();
                        //new SqlCommand("set noexec on; ", connect).ExecuteNonQuery();

                        DataTable dt = new DataTable();
                        using (SqlCommand cmd = new SqlCommand("SET SHOWPLAN_ALL ON", connect))
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();

                            cmd.CommandText = "exec " + storedProcedureName + ";";
                            cmd.CommandType = CommandType.Text;

                            cmd.ExecuteNonQuery();
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                dt.Load(reader);
                            }

                            cmd.CommandText = "SET SHOWPLAN_ALL OFF";
                            cmd.CommandType = CommandType.Text;
                        }
                    }
                    catch (Exception ex)
                    {
                        errorMessage += "<br/>Stored Procedure [" + storedProcedureName + "]: <br/>" + ex.Message.Replace(Environment.NewLine, "<br/>");
                    }
                    //new SqlCommand("set noexec off; ", connect).ExecuteNonQuery();
                    //new SqlCommand("set showplan_text off; ", connect).ExecuteNonQuery();
                }

            }
            finally
            {
                connect.Close();
            }
            if (!string.IsNullOrEmpty(errorMessage))
            {
                throw new Exception(errorMessage);
            }
        }

        private static DataSet ExecureQueryToDataset(SqlConnection connect, string script)
        {
            DataSet ds = new DataSet();
            SqlCommand command = new SqlCommand(script, connect);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            da.Fill(ds);
            return ds;
        }
    }
}
